﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    public partial class MainService : ISalaryFund
    {
        public DataSet SalaryFund_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet SalaryFund_GetDate(string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetDate", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet SalaryFund_GetList(out int ItemCount, int currentPage, int pageSize, int year, string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet SalaryFund_GetLastData(int month, int year, string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetLastData", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet SalaryFund_GetData(int month, int year, string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetData", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public SalaryFundInfo SalaryFund_GetInfo(int SalaryFundID)
        {
            SalaryFundInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SalaryFund_ID", SalaryFundID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new SalaryFundInfo();
                    retVal.ID = Convert.ToInt32(dr["SalaryFund_ID"]);
                    retVal.Month = Convert.ToDateTime(dr["SalaryFund_Month"]);
                    retVal.Quater = dr["SalaryFund_Quater"].ToString();
                    retVal.BranchCode = dr["SalaryFund_BranchCode"].ToString();
                    retVal.BHYT_Amount = Convert.ToDecimal(dr["SalaryFund_BHYTAmount"]);
                    retVal.BHYT_Employee = Convert.ToInt32(dr["SalaryFund_BHYTEmployee"]);
                    retVal.BHYT_HaveToPaid = Convert.ToDecimal(dr["SalaryFund_BHYTHaveToPaid"]);
                    retVal.BHYT_Increase = Convert.ToDecimal(dr["SalaryFund_BHYTIncrease"]);
                    retVal.BHYT_Decrease = Convert.ToDecimal(dr["SalaryFund_BHYTDecrease"]);
                    retVal.BHYT_Sum = Convert.ToDecimal(dr["SalaryFund_BHYTSum"]);
                    retVal.BHYT_PaidAmount = Convert.ToDecimal(dr["SalaryFund_BHYTPaidAmount"]);
                    retVal.BHYT_Thua = Convert.ToDecimal(dr["SalaryFund_BHYTThua"]);
                    retVal.BHYT_Thieu = Convert.ToDecimal(dr["SalaryFund_BHYTThieu"]);
                    retVal.BHYT_KyTruocThua = Convert.ToDecimal(dr["SalaryFund_BHYTKyTruocThua"]);
                    retVal.BHYT_KyTruocThieu = Convert.ToDecimal(dr["SalaryFund_BHYTKyTruocThieu"]);

                    retVal.BHTN_Amount = Convert.ToDecimal(dr["SalaryFund_BHTNAmount"]);
                    retVal.BHTN_Employee = Convert.ToInt32(dr["SalaryFund_BHTNEmployee"]);
                    retVal.BHTN_HaveToPaid = Convert.ToDecimal(dr["SalaryFund_BHTNHaveToPaid"]);
                    retVal.BHTN_Increase = Convert.ToDecimal(dr["SalaryFund_BHTNIncrease"]);
                    retVal.BHTN_Decrease = Convert.ToDecimal(dr["SalaryFund_BHTNDecrease"]);
                    retVal.BHTN_Sum = Convert.ToDecimal(dr["SalaryFund_BHTNSum"]);
                    retVal.BHTN_PaidAmount = Convert.ToDecimal(dr["SalaryFund_BHTNPaidAmount"]);
                    retVal.BHTN_Thua = Convert.ToDecimal(dr["SalaryFund_BHTNThua"]);
                    retVal.BHTN_Thieu = Convert.ToDecimal(dr["SalaryFund_BHTNThieu"]);
                    retVal.BHTN_KyTruocThua = Convert.ToDecimal(dr["SalaryFund_BHTNKyTruocThua"]);
                    retVal.BHTN_KyTruocThieu = Convert.ToDecimal(dr["SalaryFund_BHTNKyTruocThieu"]);

                    retVal.BHXH_Amount = Convert.ToDecimal(dr["SalaryFund_BHXHAmount"]);
                    retVal.BHXH_Employee = Convert.ToInt32(dr["SalaryFund_BHXHEmployee"]);
                    retVal.BHXH_HaveToPaid = Convert.ToDecimal(dr["SalaryFund_BHXHHaveToPaid"]);
                    retVal.BHXH_Increase = Convert.ToDecimal(dr["SalaryFund_BHXHIncrease"]);
                    retVal.BHXH_Decrease = Convert.ToDecimal(dr["SalaryFund_BHXHDecrease"]);
                    retVal.BHXH_Sum = Convert.ToDecimal(dr["SalaryFund_BHXHSum"]);
                    retVal.BHXH_PaidAmount = Convert.ToDecimal(dr["SalaryFund_BHXHPaidAmount"]);
                    retVal.BHXH_Thua = Convert.ToDecimal(dr["SalaryFund_BHXHThua"]);
                    retVal.BHXH_Thieu = Convert.ToDecimal(dr["SalaryFund_BHXHThieu"]);
                    retVal.BHXH_KyTruocThua = Convert.ToDecimal(dr["SalaryFund_BHXHKyTruocThua"]);
                    retVal.BHXH_KyTruocThieu = Convert.ToDecimal(dr["SalaryFund_BHXHKyTruocThieu"]);

                    retVal.PhatKyTruoc = Convert.ToDecimal(dr["SalaryFund_ChamNop_KyTruoc"]);
                    retVal.PhatKyNay = Convert.ToDecimal(dr["SalaryFund_ChamNop_KyNay"]);
                    retVal.PhatDaNop = Convert.ToDecimal(dr["SalaryFund_ChamNop_DaDong"]);
                    retVal.PhatKySau = Convert.ToDecimal(dr["SalaryFund_ChamNop_KySau"]);
                    
                    retVal.IsApproved = Convert.ToInt32(dr["SalaryFund_IsApproved"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int SalaryFund_Insert(SalaryFundInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SalaryFund_Month", eInfo.Month);
            dbCmd.Parameters.AddWithValue("@SalaryFund_Quater", eInfo.Quater);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BranchCode", eInfo.BranchCode);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTAmount", eInfo.BHYT_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTEmployee", eInfo.BHYT_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTHaveToPaid", eInfo.BHYT_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTIncrease", eInfo.BHYT_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTDecrease", eInfo.BHYT_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTPaidAmount", eInfo.BHYT_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTSum", eInfo.BHYT_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTThua", eInfo.BHYT_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTThieu", eInfo.BHYT_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTKyTruocThua", eInfo.BHYT_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTKyTruocThieu", eInfo.BHYT_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNAmount", eInfo.BHTN_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNEmployee", eInfo.BHTN_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNHaveToPaid", eInfo.BHTN_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNIncrease", eInfo.BHTN_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNDecrease", eInfo.BHTN_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNPaidAmount", eInfo.BHTN_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNSum", eInfo.BHTN_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNThua", eInfo.BHTN_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNThieu", eInfo.BHTN_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNKyTruocThua", eInfo.BHTN_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNKyTruocThieu", eInfo.BHTN_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHAmount", eInfo.BHXH_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHEmployee", eInfo.BHXH_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHHaveToPaid", eInfo.BHXH_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHIncrease", eInfo.BHXH_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHDecrease", eInfo.BHXH_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHPaidAmount", eInfo.BHXH_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHSum", eInfo.BHXH_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHThua", eInfo.BHXH_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHThieu", eInfo.BHXH_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHKyTruocThua", eInfo.BHXH_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHKyTruocThieu", eInfo.BHXH_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KyTruoc", eInfo.PhatKyTruoc);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KyNay", eInfo.PhatKyNay);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_DaDong", eInfo.PhatDaNop);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KySau", eInfo.PhatKySau);

            dbCmd.Parameters.AddWithValue("@SalaryFund_IsApproved", eInfo.IsApproved);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool SalaryFund_Update(SalaryFundInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SalaryFund_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@SalaryFund_Month", eInfo.Month);
            dbCmd.Parameters.AddWithValue("@SalaryFund_Quater", eInfo.Quater);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BranchCode", eInfo.BranchCode);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTAmount", eInfo.BHYT_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTEmployee", eInfo.BHYT_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTHaveToPaid", eInfo.BHYT_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTIncrease", eInfo.BHYT_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTDecrease", eInfo.BHYT_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTPaidAmount", eInfo.BHYT_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTSum", eInfo.BHYT_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTThua", eInfo.BHYT_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTThieu", eInfo.BHYT_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTKyTruocThua", eInfo.BHYT_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHYTKyTruocThieu", eInfo.BHYT_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNAmount", eInfo.BHTN_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNEmployee", eInfo.BHTN_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNHaveToPaid", eInfo.BHTN_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNIncrease", eInfo.BHTN_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNDecrease", eInfo.BHTN_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNPaidAmount", eInfo.BHTN_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNSum", eInfo.BHTN_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNThua", eInfo.BHTN_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNThieu", eInfo.BHTN_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNKyTruocThua", eInfo.BHTN_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHTNKyTruocThieu", eInfo.BHTN_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHAmount", eInfo.BHXH_Amount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHEmployee", eInfo.BHXH_Employee);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHHaveToPaid", eInfo.BHXH_HaveToPaid);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHIncrease", eInfo.BHXH_Increase);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHDecrease", eInfo.BHXH_Decrease);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHPaidAmount", eInfo.BHXH_PaidAmount);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHSum", eInfo.BHXH_Sum);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHThua", eInfo.BHXH_Thua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHThieu", eInfo.BHXH_Thieu);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHKyTruocThua", eInfo.BHXH_KyTruocThua);
            dbCmd.Parameters.AddWithValue("@SalaryFund_BHXHKyTruocThieu", eInfo.BHXH_KyTruocThieu);

            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KyTruoc", eInfo.PhatKyTruoc);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KyNay", eInfo.PhatKyNay);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_DaDong", eInfo.PhatDaNop);
            dbCmd.Parameters.AddWithValue("@SalaryFund_ChamNop_KySau", eInfo.PhatKySau);
           
            dbCmd.Parameters.AddWithValue("@SalaryFund_IsApproved", eInfo.IsApproved);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool SalaryFund_Delete(int SalaryFundID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SalaryFund_ID", SalaryFundID);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public int SalaryFund_CheckExist(int ID, string BranchCode, string _Quater, DateTime _date)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_SalaryFund_CheckExist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@SalaryFund_ID", ID);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@SalaryFund_Quater", _Quater);
            dbCmd.Parameters.AddWithValue("@SalaryFund_Month", _date);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = Convert.ToInt32(dr["SalaryFund_ID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
    }
}

